1. Loading the source
data into SQL Server Database was hard since source data was large.
2. Understanding
Source data without Data Dictionary.
3. Referential integrity
was missing between tables.
4. Implementing
Slowly Changing Dimension type 1 was challenging.
.
Solutions for the troubles that occurred during
different phases of the project:
1. While loading source (OLTP) data into SQL Server Database it was hard, since data was large. We had to use Command Prompt to load the data into a SQL Server Database. In the below image, you see how we used Command Prompt to load the data into SQL Server Database.
2. There was no Data
Dictionary provided to know about the definition of metadata. We need
to go through each column manually and look at each column names and values to
understand the data.
To get more clarity let's
have a look at some example in the below image :
1. Is the name of the
Database.
2. Is the name of the
Table.
3. Showing what table is
queried.
4. BDY is a column name it
means Body of the car.
5. GLS is a column name it
means Glass of the car.
3. The source data we got
doesn't have Foreign Key relationship, in the Primary Table that has Primary
key. It will help to maintain referential integrity . we had to manually check
the matching values of related tables to create Foreign Key constraint. By
having this relationship we can join multiple tables to create Data Warehouse
which has multiple Dimensional and Fact Tables. For details see the below
image.
1. Key symbol facing
down represents Primary Key.
2. In SQL Server if you
expand your Estimates database you will see option Database Diagram right click
and select New Database diagram. SQL Server will try to automatically identify
relationship. For our database it created only one relationship. But for the rest
of the tables it was not able to create relationship, therefore we created
manually.
4. As we need to unpivot 62 columns into one single column, while creating Dimensions and Fact tables by implementing Slowly Change Dimensions type 1 was challenging as execution time was more.
1. This is the Data
Warehouse Database created using a Stored Procedure.
2. Five Dimensional tables
and one Fact table created in Star Schema Model.
3. This is the Fact
table holding 62 columns data into one column.
4. You can see all
the columns details.
5. It has 5961672 rows.
1. Loading the source
data into SQL Server Database was hard since source data was large.
2. Understanding
Source data without Data Dictionary.
3. Referential integrity
was missing between tables.
4. Implementing
Slowly Changing Dimension type 1 was challenging.
.
Solutions for the troubles that occurred during
different phases of the project:
1. While loading source (OLTP) data into SQL Server Database it was hard, since data was large. We had to use Command Prompt to load the data into a SQL Server Database. In the below image, you see how we used Command Prompt to load the data into SQL Server Database.
2. There was no Data
Dictionary provided to know about the definition of metadata. We need
to go through each column manually and look at each column names and values to
understand the data.
To get more clarity let's
have a look at some example in the below image :
1. Is the name of the
Database.
2. Is the name of the
Table.
3. Showing what table is
queried.
4. BDY is a column name it
means Body of the car.
5. GLS is a column name it
means Glass of the car.
3. The source data we got
doesn't have Foreign Key relationship, in the Primary Table that has Primary
key. It will help to maintain referential integrity . we had to manually check
the matching values of related tables to create Foreign Key constraint. By
having this relationship we can join multiple tables to create Data Warehouse
which has multiple Dimensional and Fact Tables. For details see the below
image.
1. Key symbol facing
down represents Primary Key.
2. In SQL Server if you
expand your Estimates database you will see option Database Diagram right click
and select New Database diagram. SQL Server will try to automatically identify
relationship. For our database it created only one relationship. But for the rest
of the tables it was not able to create relationship, therefore we created
manually.
4. As we need to unpivot 62 columns into one single column, while creating Dimensions and Fact tables by implementing Slowly Change Dimensions type 1 was challenging as execution time was more.
1. This is the Data
Warehouse Database created using a Stored Procedure.
2. Five Dimensional tables
and one Fact table created in Star Schema Model.
3. This is the Fact
table holding 62 columns data into one column.
4. You can see all
the columns details.
5. It has 5961672 rows.